package org.example.dbcompare.service.data;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import org.example.dbcompare.model.ExportDataSqlResult;
import org.example.dbcompare.model.context.DataCompareContext;
import org.example.dbcompare.model.result.CompareDataResult;
import org.example.dbcompare.model.result.CompareDataItem;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * @author wuyong
 * @date 2025/10/31
 */
public class ExportMissDataSqlService {
    public ExportDataSqlResult exportMissDataSql(DataCompareContext compareContext, CompareDataResult compareResult) {
        ExportDataSqlResult result = new ExportDataSqlResult();
        if (compareResult == null || compareResult.compareDataItems == null || compareResult.compareDataItems.isEmpty()) {
            return result;
        }

        SimpleDataSource sourceDataSource = new SimpleDataSource(
                compareContext.getSourceDb().getUrl(),
                compareContext.getSourceDb().getUsername(),
                compareContext.getSourceDb().getPassword()
        );

        for (CompareDataItem item : compareResult.compareDataItems) {
            List<Map<String, Object>> missingKeys = item.getMismatchRows();
            if (missingKeys == null || missingKeys.isEmpty()) {
                continue;
            }
            String table = item.getTableName();
            for (Map<String, Object> keyRow : missingKeys) {
                // 查询源端完整行
                List<Entity> rows = querySourceRow(sourceDataSource, table, keyRow);
                if (rows == null || rows.isEmpty()) {
                    continue;
                }
                Entity row = rows.get(0);
                String insert = buildInsertSql(table, row);
                result.addInsertSql(insert);
            }
        }
        return result;
    }

    private List<Entity> querySourceRow(SimpleDataSource ds, String table, Map<String, Object> keyRow) {
        StringJoiner where = new StringJoiner(" AND ");
        List<Object> params = new ArrayList<>();
        for (Map.Entry<String, Object> e : keyRow.entrySet()) {
            if (e.getValue() == null) {
                where.add(e.getKey() + " IS NULL");
            } else {
                where.add(e.getKey() + " = ?");
                params.add(e.getValue());
            }
        }
        String sql = "SELECT * FROM " + table + (where.length() > 0 ? " WHERE " + where : "");
        try {
            return Db.use(ds).query(sql, params.toArray());
        } catch (Exception ex) {
            return new ArrayList<>();
        }
    }

    private String buildInsertSql(String table, Entity row) {
        // 使用 LinkedHashMap 保持列顺序（Entity 内部已是有序映射，兜底拷贝）
        Map<String, Object> data = new LinkedHashMap<>(row);
        StringJoiner cols = new StringJoiner(", ");
        StringJoiner vals = new StringJoiner(", ");
        for (Map.Entry<String, Object> e : data.entrySet()) {
            cols.add("`" + e.getKey() + "`");
            vals.add(sqlLiteral(e.getValue()));
        }
        return "INSERT INTO " + table + " (" + cols + ") VALUES (" + vals + ");";
    }

    private String sqlLiteral(Object v) {
        if (v == null) {
            return "NULL";
        }
        if (v instanceof Number) {
            return v.toString();
        }
        if (v instanceof Boolean) {
            return ((Boolean) v) ? "1" : "0";
        }
        String s = String.valueOf(v);
        // 简单转义单引号
        s = s.replace("'", "''");
        return "'" + s + "'";
    }
}
